import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.colors import LinearSegmentedColormap
import matplotlib.patches as patches
import numpy as np
import os

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# === 你提供的准确文件路径 ===
DATA_FILE = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\data\erp_order_data.xlsx"
RESULTS_DIR = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\results"
os.makedirs(RESULTS_DIR, exist_ok=True)

def create_mean_barchart():
    # 1. 读取你指定路径的数据
    print(f"正在从以下路径读取数据:\n{DATA_FILE}")
    df = pd.read_excel(DATA_FILE)
    print(f"✅ 成功加载 {len(df)} 条订单记录")

    # 2. 按省份汇总销售金额
    sales_by_region = df.groupby('province')['product_amount'].sum().sort_values(ascending=False)
    regions = sales_by_region.index.tolist()
    sales = sales_by_region.values.tolist()
    mean_sales = np.mean(sales)

    # 3. 动态生成标题（完全基于你的数据）
    top_region = regions[0]
    top_pct = sales[0] / sum(sales) * 100
    bottom_region = regions[-1]
    bottom_pct = sales[-1] / sum(sales) * 100
    title_main = "各地区销售金额分布"
    title_sub = f"{top_region}销售最高，占比{top_pct:.1f}%；{bottom_region}占比{bottom_pct:.1f}%"

    # 4. 创建图表（保持靛蓝背景风格）
    fig = plt.figure(figsize=(14, 9), facecolor='#1A1A2E')
    ax = fig.add_subplot(111, facecolor='#1A1A2E')

    # 渐变色
    colors = ['#16213E', '#0F3460', '#1A5F7A', '#2C8C99', '#4BB5C2', '#7FD6E3']
    cmap = LinearSegmentedColormap.from_list('ocean_blue', colors, N=100)

    bar_width = 0.7
    x_pos = np.arange(len(regions))

    # 绘制渐变柱子
    for i, (x, sale) in enumerate(zip(x_pos, sales)):
        gradient_height = 80
        for j in range(gradient_height):
            height_segment = sale / gradient_height
            y_bottom = j * height_segment
            color = cmap(1 - (j / gradient_height) * 0.7)
            rect = patches.Rectangle(
                (x - bar_width/2, y_bottom), bar_width, height_segment,
                linewidth=0, facecolor=color, alpha=0.9
            )
            ax.add_patch(rect)

    # 添加白色边框
    for i in x_pos:
        ax.bar([i], [sales[i]], width=bar_width, color='none', edgecolor='#E0E0E0', linewidth=2)

    # 5. 添加平均值线
    ax.axhline(y=mean_sales, color='orange', linestyle='-', linewidth=2)
    ax.text(len(regions) - 0.5, mean_sales + max(sales) * 0.01, f'平均值: {mean_sales:,.0f}',
            fontsize=14, fontweight='bold', color='orange', ha='right', va='bottom')

    # 6. 设置样式
    ax.set_title(f"{title_main}\n{title_sub}", fontsize=20, fontweight='bold', pad=25, color='#FFFFFF')
    ax.set_ylabel('销售金额 (元)', fontsize=18, fontweight='bold', color='#E0E0E0')
    ax.set_xlabel('地区', fontsize=16, fontweight='bold', color='#E0E0E0')
    ax.set_xticks(x_pos)
    ax.set_xticklabels(regions, fontsize=14, fontweight='bold', color='#E0E0E0')
    ax.tick_params(axis='y', labelsize=14, colors='#E0E0E0')

    for spine in ax.spines.values():
        spine.set_color('#4A4A6A')
        spine.set_linewidth(2)

    ax.grid(axis='y', alpha=0.3, linestyle='--', color='#4A4A6A')
    ax.set_axisbelow(True)
    ax.set_ylim(0, max(sales) * 1.15)

    # 7. 数据标签（仅显示数字）
    for i, v in enumerate(sales):
        ax.text(i, v + max(sales) * 0.03, f'{v:,.0f}',  # 去掉所有符号
                ha='center', va='bottom',
                fontsize=14, fontweight='bold', color='#FFFFFF',
                bbox=dict(boxstyle='round,pad=0.3', facecolor='#2C3E50', edgecolor='none', alpha=0.7))

    # 8. 数据来源（使用数据中的最新日期）
    latest_date = pd.to_datetime(df['order_time']).max().strftime('%Y-%m-%d')
    ax.text(0.02, 0.98, f'数据来源：ERP订单系统，统计截至{latest_date}',
            transform=ax.transAxes, fontsize=12, color='#B0B0B0', alpha=0.7, va='top')

    plt.tight_layout()

    # 9. 保存图表
    output_path = os.path.join(RESULTS_DIR, '02_带均值柱形图.png')
    plt.savefig(output_path, dpi=300, bbox_inches='tight', facecolor='#1A1A2E', edgecolor='none')
    plt.show()

    print(f"✅ 第二张图已成功保存至:\n{output_path}")

if __name__ == "__main__":
    create_mean_barchart()